﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetJump_V3]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetJump_V3];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetJump_V3]
    @FlowID int,
    @StepID int
AS
BEGIN
SET NOCOUNT ON;

-- get all jumps within the given step.
DECLARE @Style_ID int;

SET @Style_ID = 0;
SELECT @Style_ID = Style_id FROM dbo.UDS_Flow WHERE Flow_id = @flowid;
IF ( @@ROWCOUNT = 0 )
    RETURN -1;  -- Flow not found

DECLARE @tempDescription TABLE
(
    field_name nvarchar(100) COLLATE Chinese_PRC_CI_AS,
    field_description nvarchar(300) COLLATE Chinese_PRC_CI_AS
);
INSERT INTO @tempDescription SELECT Field_name, Field_Description FROM dbo.UDS_Flow_Style_Description WHERE Style_id = @style_id;
INSERT INTO @tempDescription VALUES(N'caste', N'《职级》')

DECLARE @tempStep TABLE (
    Flow_ID int,
    Step_ID int,
    step_name nvarchar(300) COLLATE Chinese_PRC_CI_AS
);
INSERT INTO @tempStep SELECT Flow_id, step_id, step_name FROM dbo.UDS_Flow_Step WHERE Flow_id = @flowid;    
INSERT INTO @tempStep (Flow_id, step_id, step_name) VALUES(@flowid, 0, N'《文档结束》'); -- 0 indicates a special step


SELECT
        a.*,
        c.field_description,
        dbo.fn_flow_get_jump_target_name(@flowid, a.To_Flow_ID, a.To_Step_ID) as StepTo_Name
    FROM 
        dbo.UDS_Flow_Jump a,
        @tempDescription c
    WHERE
        c.field_name = a.fieldname
        and a.flow_id = @flowid
        and a.step_id = @stepid
    ORDER BY a.Priority;

END
GO